/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     10/18/2012 9:20:41 AM                        */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BAN') and o.name = 'FK_BAN_CO_KHU_VUC')
alter table BAN
   drop constraint FK_BAN_CO_KHU_VUC
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BAN') and o.name = 'FK_BAN_CO2_HOA_DON')
alter table BAN
   drop constraint FK_BAN_CO2_HOA_DON
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CT_HOADON') and o.name = 'FK_CT_HOADO_CT_HOADON_MON')
alter table CT_HOADON
   drop constraint FK_CT_HOADO_CT_HOADON_MON
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('CT_HOADON') and o.name = 'FK_CT_HOADO_CT_HOADON_HOA_DON')
alter table CT_HOADON
   drop constraint FK_CT_HOADO_CT_HOADON_HOA_DON
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GOIMON') and o.name = 'FK_GOIMON_GOIMON_BAN')
alter table GOIMON
   drop constraint FK_GOIMON_GOIMON_BAN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('GOIMON') and o.name = 'FK_GOIMON_GOIMON2_MON')
alter table GOIMON
   drop constraint FK_GOIMON_GOIMON2_MON
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HOA_DON') and o.name = 'FK_HOA_DON_CO4_BAN')
alter table HOA_DON
   drop constraint FK_HOA_DON_CO4_BAN
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('HOA_DON') and o.name = 'FK_HOA_DON_LAP_NHAN_VIE')
alter table HOA_DON
   drop constraint FK_HOA_DON_LAP_NHAN_VIE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('MON') and o.name = 'FK_MON_CO23_LOAI_MON')
alter table MON
   drop constraint FK_MON_CO23_LOAI_MON
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('NHAN_VIEN') and o.name = 'FK_NHAN_VIE_CO3_CHUCVU')
alter table NHAN_VIEN
   drop constraint FK_NHAN_VIE_CO3_CHUCVU
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PHUC_VU') and o.name = 'FK_PHUC_VU_PHUC_VU_NHAN_VIE')
alter table PHUC_VU
   drop constraint FK_PHUC_VU_PHUC_VU_NHAN_VIE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('PHUC_VU') and o.name = 'FK_PHUC_VU_PHUC_VU2_KHU_VUC')
alter table PHUC_VU
   drop constraint FK_PHUC_VU_PHUC_VU2_KHU_VUC
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('BAN')
            and   name  = 'CO2_FK'
            and   indid > 0
            and   indid < 255)
   drop index BAN.CO2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('BAN')
            and   name  = 'CO_FK'
            and   indid > 0
            and   indid < 255)
   drop index BAN.CO_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BAN')
            and   type = 'U')
   drop table BAN
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CHUCVU')
            and   type = 'U')
   drop table CHUCVU
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CT_HOADON')
            and   name  = 'CT_HOADON2_FK'
            and   indid > 0
            and   indid < 255)
   drop index CT_HOADON.CT_HOADON2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('CT_HOADON')
            and   name  = 'CT_HOADON_FK'
            and   indid > 0
            and   indid < 255)
   drop index CT_HOADON.CT_HOADON_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CT_HOADON')
            and   type = 'U')
   drop table CT_HOADON
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GOIMON')
            and   name  = 'GOIMON2_FK'
            and   indid > 0
            and   indid < 255)
   drop index GOIMON.GOIMON2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('GOIMON')
            and   name  = 'GOIMON_FK'
            and   indid > 0
            and   indid < 255)
   drop index GOIMON.GOIMON_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('GOIMON')
            and   type = 'U')
   drop table GOIMON
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('HOA_DON')
            and   name  = 'CO4_FK'
            and   indid > 0
            and   indid < 255)
   drop index HOA_DON.CO4_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('HOA_DON')
            and   name  = 'LAP_FK'
            and   indid > 0
            and   indid < 255)
   drop index HOA_DON.LAP_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('HOA_DON')
            and   type = 'U')
   drop table HOA_DON
go

if exists (select 1
            from  sysobjects
           where  id = object_id('KHU_VUC')
            and   type = 'U')
   drop table KHU_VUC
go

if exists (select 1
            from  sysobjects
           where  id = object_id('LOAI_MON')
            and   type = 'U')
   drop table LOAI_MON
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('MON')
            and   name  = 'CO23_FK'
            and   indid > 0
            and   indid < 255)
   drop index MON.CO23_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('MON')
            and   type = 'U')
   drop table MON
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('NHAN_VIEN')
            and   name  = 'CO3_FK'
            and   indid > 0
            and   indid < 255)
   drop index NHAN_VIEN.CO3_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('NHAN_VIEN')
            and   type = 'U')
   drop table NHAN_VIEN
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PHUC_VU')
            and   name  = 'PHUC_VU2_FK'
            and   indid > 0
            and   indid < 255)
   drop index PHUC_VU.PHUC_VU2_FK
go

if exists (select 1
            from  sysindexes
           where  id    = object_id('PHUC_VU')
            and   name  = 'PHUC_VU_FK'
            and   indid > 0
            and   indid < 255)
   drop index PHUC_VU.PHUC_VU_FK
go

if exists (select 1
            from  sysobjects
           where  id = object_id('PHUC_VU')
            and   type = 'U')
   drop table PHUC_VU
go

/*==============================================================*/
/* Table: BAN                                                   */
/*==============================================================*/
create table BAN (
   MABAN                int                  not null,
   MAKV                 int                  not null,
   MAHD                 int                  not null,
   TENBAN               char(50)             null,
   TRANGTHAI            char(10)             null,
   constraint PK_BAN primary key nonclustered (MABAN)
)
go

/*==============================================================*/
/* Index: CO_FK                                                 */
/*==============================================================*/
create index CO_FK on BAN (
MAKV ASC
)
go

/*==============================================================*/
/* Index: CO2_FK                                                */
/*==============================================================*/
create index CO2_FK on BAN (
MAHD ASC
)
go

/*==============================================================*/
/* Table: CHUCVU                                                */
/*==============================================================*/
create table CHUCVU (
   TENCV                char(20)             null,
   LUONG                money                null,
   MACV                 char(10)             not null,
   constraint PK_CHUCVU primary key nonclustered (MACV)
)
go

/*==============================================================*/
/* Table: CT_HOADON                                             */
/*==============================================================*/
create table CT_HOADON (
   MAMON                int                  not null,
   MAHD                 int                  not null,
   SOLUONG              int                  null,
   DONGIA               money                null,
   DOANHTHU             money                null,
   constraint PK_CT_HOADON primary key (MAMON, MAHD)
)
go

/*==============================================================*/
/* Index: CT_HOADON_FK                                          */
/*==============================================================*/
create index CT_HOADON_FK on CT_HOADON (
MAMON ASC
)
go

/*==============================================================*/
/* Index: CT_HOADON2_FK                                         */
/*==============================================================*/
create index CT_HOADON2_FK on CT_HOADON (
MAHD ASC
)
go

/*==============================================================*/
/* Table: GOIMON                                                */
/*==============================================================*/
create table GOIMON (
   MABAN                int                  not null,
   MAMON                int                  not null,
   SOLUONG              int                  null,
   constraint PK_GOIMON primary key (MABAN, MAMON)
)
go

/*==============================================================*/
/* Index: GOIMON_FK                                             */
/*==============================================================*/
create index GOIMON_FK on GOIMON (
MABAN ASC
)
go

/*==============================================================*/
/* Index: GOIMON2_FK                                            */
/*==============================================================*/
create index GOIMON2_FK on GOIMON (
MAMON ASC
)
go

/*==============================================================*/
/* Table: HOA_DON                                               */
/*==============================================================*/
create table HOA_DON (
   NGAYHD               datetime             null,
   MAHD                 int                  not null,
   MANV                 int                  null,
   MABAN                int                  not null,
   TONGTIEN             money                null,
   constraint PK_HOA_DON primary key nonclustered (MAHD)
)
go

/*==============================================================*/
/* Index: LAP_FK                                                */
/*==============================================================*/
create index LAP_FK on HOA_DON (
MANV ASC
)
go

/*==============================================================*/
/* Index: CO4_FK                                                */
/*==============================================================*/
create index CO4_FK on HOA_DON (
MABAN ASC
)
go

/*==============================================================*/
/* Table: KHU_VUC                                               */
/*==============================================================*/
create table KHU_VUC (
   MAKV                 int                  not null,
   TENKV                char(50)             null,
   PHUTHU               money                null,
   constraint PK_KHU_VUC primary key nonclustered (MAKV)
)
go

/*==============================================================*/
/* Table: LOAI_MON                                              */
/*==============================================================*/
create table LOAI_MON (
   MALOAIMON            int                  not null,
   TENLOAIMON           char(50)             null,
   constraint PK_LOAI_MON primary key nonclustered (MALOAIMON)
)
go

/*==============================================================*/
/* Table: MON                                                   */
/*==============================================================*/
create table MON (
   MAMON                int                  not null,
   MALOAIMON            int                  not null,
   TENMON               char(50)             null,
   DONGIA               money                null,
   constraint PK_MON primary key nonclustered (MAMON)
)
go

/*==============================================================*/
/* Index: CO23_FK                                               */
/*==============================================================*/
create index CO23_FK on MON (
MALOAIMON ASC
)
go

/*==============================================================*/
/* Table: NHAN_VIEN                                             */
/*==============================================================*/
create table NHAN_VIEN (
   TENNV                char(50)             null,
   MANV                 int                  not null,
   MACV                 char(10)             null,
   DIACHI               char(50)             null,
   CMND                 int                  null,
   DIENTHOAI            int                  null,
   NGAYSINH             datetime             null,
   NGAYLAM              int                  null,
   constraint PK_NHAN_VIEN primary key nonclustered (MANV)
)
go

/*==============================================================*/
/* Index: CO3_FK                                                */
/*==============================================================*/
create index CO3_FK on NHAN_VIEN (
MACV ASC
)
go

/*==============================================================*/
/* Table: PHUC_VU                                               */
/*==============================================================*/
create table PHUC_VU (
   MANV                 int                  not null,
   MAKV                 int                  not null,
   CATRUC               int                  null,
   constraint PK_PHUC_VU primary key (MANV, MAKV)
)
go

/*==============================================================*/
/* Index: PHUC_VU_FK                                            */
/*==============================================================*/
create index PHUC_VU_FK on PHUC_VU (
MANV ASC
)
go

/*==============================================================*/
/* Index: PHUC_VU2_FK                                           */
/*==============================================================*/
create index PHUC_VU2_FK on PHUC_VU (
MAKV ASC
)
go

alter table BAN
   add constraint FK_BAN_CO_KHU_VUC foreign key (MAKV)
      references KHU_VUC (MAKV)
go

alter table BAN
   add constraint FK_BAN_CO2_HOA_DON foreign key (MAHD)
      references HOA_DON (MAHD)
go

alter table CT_HOADON
   add constraint FK_CT_HOADO_CT_HOADON_MON foreign key (MAMON)
      references MON (MAMON)
go

alter table CT_HOADON
   add constraint FK_CT_HOADO_CT_HOADON_HOA_DON foreign key (MAHD)
      references HOA_DON (MAHD)
go

alter table GOIMON
   add constraint FK_GOIMON_GOIMON_BAN foreign key (MABAN)
      references BAN (MABAN)
go

alter table GOIMON
   add constraint FK_GOIMON_GOIMON2_MON foreign key (MAMON)
      references MON (MAMON)
go

alter table HOA_DON
   add constraint FK_HOA_DON_CO4_BAN foreign key (MABAN)
      references BAN (MABAN)
go

alter table HOA_DON
   add constraint FK_HOA_DON_LAP_NHAN_VIE foreign key (MANV)
      references NHAN_VIEN (MANV)
go

alter table MON
   add constraint FK_MON_CO23_LOAI_MON foreign key (MALOAIMON)
      references LOAI_MON (MALOAIMON)
go

alter table NHAN_VIEN
   add constraint FK_NHAN_VIE_CO3_CHUCVU foreign key (MACV)
      references CHUCVU (MACV)
go

alter table PHUC_VU
   add constraint FK_PHUC_VU_PHUC_VU_NHAN_VIE foreign key (MANV)
      references NHAN_VIEN (MANV)
go

alter table PHUC_VU
   add constraint FK_PHUC_VU_PHUC_VU2_KHU_VUC foreign key (MAKV)
      references KHU_VUC (MAKV)
go

